import geopandas as gpd
from sqlalchemy import create_engine, text
from sqlalchemy_utils import create_database, database_exists, drop_database
import pandas as pd
import sqlalchemy
import json
from bokeh.io import show,output_notebook
from bokeh.models import (CDSView, ColorBar, ColumnDataSource,
CustomJS, CustomJSFilter,
GeoJSONDataSource, HoverTool,
LinearColorMapper, LogColorMapper, Slider)
from bokeh.layouts import column, row, widgetbox
from bokeh.palettes import brewer
from bokeh.plotting import figure
#Variables
db_type = "postgresql" #postgresql
username = "postgres"
password = "pass"
host = "localhost"
port = "5432"
db_name = "coviddistribution"
#Put it together
engine = create_engine(f"{db_type}://{username}:{password}@{host}:{port}/{db_name}")
inspector = sqlalchemy.inspect(engine)
inspector.get_table_names() #We see that our table was added to our database
#SQL query
sql = """
SELECT
covid_cases.county,
covid_cases.state,
covid_cases.cases,
demographics_county."POPESTIMATE2019",
uscounties.geometry as geometry
FROM
covid_cases
INNER JOIN
uscounties ON covid_cases.fips=CAST(uscounties."FIPS" AS BIGINT)
INNER JOIN
demographics_county ON covid_cases.fips=demographics_county."FIPS"
"""
#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"
#Execute query to create GeoDataFrame
covid_cases = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
# covid_cases = pd.read_sql(sql,engine)
covid_cases
covid_cases["cases_per_100k"] = (covid_cases["cases"] * 100000) / covid_cases["POPESTIMATE2019"]
covid_cases = covid_cases[(covid_cases["state"]!="Alaska") & (covid_cases["state"] != "Hawaii")]
covid_cases
output_notebook()
def get_geodatasource(gdf):
"""Get getjsondatasource from geopandas object"""
json_data = json.dumps(json.loads(gdf.to_json()))
return GeoJSONDataSource(geojson = json_data)
def plot_choropleth(gdf,color_palette,column,title,tooltips=None,reverse_palette=True,use_min_color=None,use_max_color=None,add_vaccine=None,add_vaccine_df=None):
geosource = get_geodatasource(gdf)
palette = color_palette #brewer['YlOrBr'][8]
if (reverse_palette):
palette = palette[::-1]
vals = gdf[column]
if (not use_min_color):
use_min_color = vals.min()
if (not use_max_color):
use_max_color = vals.max()
color_mapper = LinearColorMapper(palette=palette, low=use_min_color, high = use_max_color)
color_bar = ColorBar(color_mapper=color_mapper,label_standoff=8,width=500,height=20,orientation='horizontal')
tools='wheel_zoom,pan,reset,save'
p = figure(title=title,
plot_height=500,
plot_width=700,
toolbar_location='left',
tools=tools,
output_backend="webgl")
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None
p.axis.visible = False
covid_cases_plot = p.patches('xs','ys',
source=geosource,
fill_alpha=1,
line_width=0.5,
line_color='black',
fill_color={'field':column,
'transform':color_mapper}
)
if (tooltips):
# Create hover tool
p.add_tools(HoverTool(renderers = [covid_cases_plot],
tooltips = tooltips))
p.add_layout(color_bar,'below')
if (add_vaccine!=None):
vaccine_nn = add_vaccine_df
sites = p.circle('x','y',source=ColumnDataSource({"x":vaccine_nn.geometry.x,"y":vaccine_nn.geometry.y,"icao":vaccine_nn["icao"],"dist":vaccine_nn["dist"],"siteid":vaccine_nn["siteid"],"vaccines_allocated_per_cvs":vaccine_nn["vaccines_allocated_per_cvs"]}),color='#0B0B0B',size=3,alpha=0.5,legend_label="Vaccination Sites")
# Create hover tool
p.add_tools(HoverTool(renderers = [sites],
tooltips = [('siteID',"@siteid"),("vaccines_allocated_cvs","@vaccines_allocated_per_cvs"),('Nearest Airport (Distribution Center)','@icao'),('Straight Line Distance in Miles','@dist')]))
return p
show(plot_choropleth(covid_cases,
brewer['YlOrBr'][8],
"cases_per_100k",
"Covid Cases per 100K residents by County as of Oct. 8th 2021",
tooltips=[('County','@county'),
('State','@state'),
('Population','@POPESTIMATE2019'),
('Cases','@cases'),
('Cases Per 100K Residents','@cases_per_100k')
]))
#SQL query
sql = """
SELECT
uscounties."NAME" as county,
uscounties."STATE_NAME" as state,
demographics_county."POPESTIMATE2019" as population,
demographics_county."Minority_Pct",
uscounties.geometry as geometry
FROM
demographics_county
INNER JOIN
uscounties ON demographics_county."FIPS"=CAST(uscounties."FIPS" AS BIGINT)
WHERE
uscounties."STATE_NAME"!='Alaska' AND
uscounties."STATE_NAME"!='Hawaii'
"""
#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"
#Execute query to create GeoDataFrame
minority_pct = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
minority_pct
show(
plot_choropleth(
minority_pct,
brewer['PuBu'][6],
"Minority_Pct",
"Percentage of Minorities by County",
tooltips=[
('County','@county'),
('State','@state'),
('Population','@population'),
('Minority Percentage','@Minority_Pct')
]
)
)
#SQL query
sql = """
SELECT
uscounties."NAME" as county,
uscounties."STATE_NAME" as state,
demographics_county."POPESTIMATE2019" as population,
demographics_county."Minority_Pct" as minority_pct,
vaccination_county."Series_Complete_Pop_Pct" as vaccination_pct,
uscounties.geometry as geometry
FROM
vaccination_county
INNER JOIN
uscounties ON vaccination_county."FIPS"=CAST(uscounties."FIPS" AS BIGINT)
INNER JOIN
demographics_county ON vaccination_county."FIPS"=demographics_county."FIPS"
WHERE
uscounties."STATE_NAME"!='Alaska' AND
uscounties."STATE_NAME"!='Hawaii'
"""
#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"
#Execute query to create GeoDataFrame
vac_cty = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
vac_cty
show(
plot_choropleth(
vac_cty,
brewer['RdYlGn'][6],
"vaccination_pct",
"Percentage of Vaccinated Residents by County (Note 0% indicates non-reporting counties)",
tooltips=[
('County','@county'),
('State','@state'),
('Population','@population'),
('Percent of Vaccinated Residents','@vaccination_pct')
],
reverse_palette=True
)
)
#SQL query
sql = """
SELECT
airports.icao,
airports.name,
airports.geometry AS geometry
FROM
airports
WHERE
airports.is_military=1
"""
#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"
#Execute query to create GeoDataFrame
military_airports = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
military_airports
#SQL query
sql = """
SELECT
airports.icao,
airports.name,
airports.geometry AS geometry
FROM
airports
WHERE
airports.is_military=0
"""
#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"
#Execute query to create GeoDataFrame
non_military_airports = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
non_military_airports = non_military_airports[~(non_military_airports["name"].str.contains("Army")) & ~(non_military_airports["name"].str.contains("Navy")) & ~(non_military_airports["name"].str.contains("Air Force")) & ~(non_military_airports["name"].str.contains("Joint Base"))]
non_military_airports
#SQL query
sql = """
SELECT
DISTINCT ON (vaccine_locations."SiteID")
airports.icao,
vaccine_locations."SiteID" as siteid,
vaccine_locations."STATE_NAME" as state,
vaccine_locations."NAME" as county,
vaccine_locations."FIPS" as fips,
ABS(ST_Distance(ST_Transform(airports.geometry,3857),ST_Transform(vaccine_locations.geometry,3857)) * 0.0006213712) as dist,
vaccine_locations.geometry as geometry
FROM
airports
INNER JOIN
vaccine_locations ON
airports.state_fips=vaccine_locations."STATE_FIPS"
WHERE
vaccine_locations."STATE_NAME"!='Alaska' AND
vaccine_locations."STATE_NAME"!='Hawaii'
ORDER BY
siteid ASC,
dist ASC
"""
#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"
#Execute query to create GeoDataFrame
vaccine_nn = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
vaccine_nn
# Create figure object.
p = figure(title = 'US Counties, Vaccination Sites and Medium to Large Airports',
plot_height = 600 ,
plot_width = 950,
toolbar_location = 'left',
tools = "pan, wheel_zoom, reset, save",
output_backend="webgl")
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None
# Add patch renderer to figure.
states = p.patches('xs','ys', source = get_geodatasource(minority_pct),
fill_color = '#171717',
line_color = 'gray',
line_width = 0.25,
fill_alpha = 1)
p.add_tools(HoverTool(renderers=[states],
tooltips = [('State','@state'),
('County','@county'),
('Population','@population')]))
sites = p.circle('x','y',source=ColumnDataSource({"x":vaccine_nn.geometry.x,"y":vaccine_nn.geometry.y,"icao":vaccine_nn["icao"],"dist":vaccine_nn["dist"],"siteid":vaccine_nn["siteid"]}),color='#FF4D2A',size=3,alpha=0.5,legend_label="Vaccination Sites")
# Create hover tool
p.add_tools(HoverTool(renderers = [sites],
tooltips = [('siteID',"@siteid"),('Nearest Airport (Distribution Center)','@icao'),('Straight Line Distance in Miles','@dist')]))
airports_military = p.circle('x','y',source=ColumnDataSource({"x":military_airports.geometry.x,"y":military_airports.geometry.y,"airportICAO":military_airports["icao"],"airportName":military_airports["name"]}),color="#04C4A1",size=8,alpha=0.7,legend_label="Military Airports")
# Create hover tool
p.add_tools(HoverTool(renderers = [airports_military],
tooltips = [('Airport Name','@airportName'),('Airport ICAO','@airportICAO')]))
airports_non_military = p.circle('x','y',source=ColumnDataSource({"x":non_military_airports.geometry.x,"y":non_military_airports.geometry.y,"airportICAO":non_military_airports["icao"],"airportName":non_military_airports["name"]}),color="#FFE98A",size=5,alpha=0.7,legend_label="Non-Military Airports")
# Create hover tool
p.add_tools(HoverTool(renderers = [airports_non_military],
tooltips = [('Airport Name','@airportName'),('Airport ICAO','@airportICAO')]))
p.legend.location = "bottom_right"
p.legend.click_policy="hide"
p.background_fill_color = "#161616"
p.background_fill_alpha = 0.9
p.axis.visible = False
show(p)
#SQL query
sql = """
SELECT
uscounties."NAME" as county,
uscounties."STATE_NAME" as state,
CAST(uscounties."FIPS" AS BIGINT) as fips,
demographics_county."POPESTIMATE2019" as population,
demographics_county."Minority_Pct" as minority_pct,
CAST(FLOOR((demographics_county."POPESTIMATE2019" - vaccination_county."Series_Complete_Yes" - covid_cases.cases) * 0.7) AS INT) as vaccines_allocated,
uscounties.geometry as geometry
FROM
vaccination_county
INNER JOIN
uscounties ON vaccination_county."FIPS"=CAST(uscounties."FIPS" AS BIGINT)
INNER JOIN
demographics_county ON vaccination_county."FIPS"=demographics_county."FIPS"
INNER JOIN
covid_cases ON vaccination_county."FIPS"=covid_cases.fips
WHERE
uscounties."STATE_NAME"!='Alaska' AND
uscounties."STATE_NAME"!='Hawaii'
"""
#Specify name of column which stores our geometry! In table `streets_chapter_11`, the geometry is stored in a col called `geometry`
geom_col = "geometry"
#Execute query to create GeoDataFrame
allocated_vaccines = gpd.GeoDataFrame.from_postgis(sql=sql, con=engine, geom_col=geom_col)
allocated_vaccines["vaccines_allocated"] = allocated_vaccines["vaccines_allocated"].clip(lower=0)
national_non_minmaj_vac_pct = vac_cty[vac_cty["minority_pct"]<0.5]["vaccination_pct"].mean() / 100
national_minmaj_vac_pct = vac_cty[vac_cty["minority_pct"]>=0.5]["vaccination_pct"].mean() / 100
allocated_vaccines.loc[(allocated_vaccines["state"]=="Texas") & (allocated_vaccines["minority_pct"] < 0.5),"vaccines_allocated"] *= (1-national_non_minmaj_vac_pct) * 0.7
allocated_vaccines.loc[(allocated_vaccines["state"]=="Texas") & (allocated_vaccines["minority_pct"] >= 0.5),"vaccines_allocated"] *= (1-national_minmaj_vac_pct) * 0.7
allocated_vaccines["vaccines_allocated"] = allocated_vaccines["vaccines_allocated"].astype(int)
allocated_vaccines[(allocated_vaccines["state"]=="Texas")]
cvs_counts = pd.DataFrame(vaccine_nn["fips"].value_counts())
cvs_counts["counts"] =cvs_counts["fips"]
cvs_counts["fips"] = cvs_counts.index
# cvs_counts
cvs_counts = pd.merge(cvs_counts,vaccine_nn,on="fips")
cvs_counts = pd.merge(cvs_counts,allocated_vaccines,on="fips")
cvs_counts["vaccines_allocated_per_csv"] = round(cvs_counts["vaccines_allocated"] / cvs_counts["counts"])
cvs_counts = cvs_counts[["siteid","icao","dist","state_x","county_x","fips","population","minority_pct","vaccines_allocated_per_csv","geometry_x"]]
cvs_counts.columns = ["siteid","icao","dist","state","county","fips","population","minory_pct","vaccines_allocated_per_cvs","geometry"]
cvs_counts = gpd.GeoDataFrame(cvs_counts)
cvs_counts
show(
plot_choropleth(
allocated_vaccines,
brewer['RdYlGn'][11],
"vaccines_allocated",
"Allocated Vaccines Per County",
tooltips=[
('County','@county'),
('State','@state'),
('Population','@population'),
('Minority Percent','@minority_pct'),
('Vaccines Allocated','@vaccines_allocated')
],
use_min_color=0,
reverse_palette=False,
use_max_color=50000,
add_vaccine=True,
add_vaccine_df=cvs_counts
)
)